from typing import List, Optional, Tuple
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, insert, update, delete, and_
from sqlalchemy.orm import selectinload

from basic.models import (
    LogicalFile,
    LogicalVersion,
    RelationEntryFile,
    RelationFileVersion
)
from basic.database import generate_random_string
from basic import logger

async def create_file(db: AsyncSession, name: str, object_key: str) -> LogicalFile:
    file_key = generate_random_string(8)
    stmt = (
        insert(LogicalFile)
        .values(
            key=file_key,
            name=name
        )
        .returning(LogicalFile)
    )
    result = await db.execute(stmt)
    new_file = result.scalar_one()
    logger.info(f"create_file: {new_file}")
    
    version_key = generate_random_string(8) 
    stmt = insert(LogicalVersion).values(
        key=version_key,
    ).returning(LogicalVersion)
    result = await db.execute(stmt)
    new_version = result.scalar_one()
    logger.info(f"new_version: {new_version}")
    
    stmt = insert(RelationFileVersion).values(
        file_key=file_key,
        version_key=version_key,
        is_top=True
    )
    await db.execute(stmt)
    
    return new_file

async def get_file(db: AsyncSession, file_key: str) -> Optional[LogicalFile]:
    """
    获取文件详情
    
    Args:
        db: 数据库会话
        file_key: 文件键
        
    Returns:
        文件对象或None
    """
    stmt = (
        select(LogicalFile)
        .options(selectinload(LogicalFile.top_version))
        .options(selectinload(LogicalFile.versions))
        .where(LogicalFile.key == file_key)
    )
    result = await db.execute(stmt)
    file = result.scalar_one_or_none()
    return file

async def update_file(db: AsyncSession, file_key: str, name: Optional[str] = None) -> Optional[LogicalFile]:
    """
    更新文件信息
    
    Args:
        db: 数据库会话
        file_key: 文件键
        name: 新名称(可选)
        
    Returns:
        更新后的文件对象或None
    """
    # 构建更新值
    values = {}
    if name:
        values["name"] = name
    
    if not values:  # 如果没有要更新的值，则直接返回文件
        return await get_file(db, file_key)
    
    # 更新文件
    stmt = (
        update(LogicalFile)
        .where(LogicalFile.key == file_key)
        .values(**values)
        .returning(LogicalFile)
    )
    result = await db.execute(stmt)
    updated_file = result.scalar_one_or_none()
    
    return updated_file

async def delete_file(db: AsyncSession, file_key: str) -> bool:
    """
    删除文件及其关联
    
    Args:
        db: 数据库会话
        file_key: 文件键
        
    Returns:
        是否成功删除
    """
    # 1. 首先删除文件与条目的关联
    entry_file_stmt = delete(RelationEntryFile).where(
        RelationEntryFile.file_key == file_key
    )
    await db.execute(entry_file_stmt)
    
    # 2. 删除文件与版本的关联
    file_version_stmt = delete(RelationFileVersion).where(
        RelationFileVersion.file_key == file_key
    )
    await db.execute(file_version_stmt)
    
    # 3. 删除文件记录
    file_stmt = delete(LogicalFile).where(
        LogicalFile.key == file_key
    )
    result = await db.execute(file_stmt)
    
    # 如果删除了记录，则返回True，否则返回False
    return result.rowcount > 0

async def link_file_to_entry(db: AsyncSession, file_key: str, entry_key: str) -> bool:
    """
    将文件关联到条目
    
    Args:
        db: 数据库会话
        file_key: 文件键
        entry_key: 条目键
        
    Returns:
        是否成功关联
    """
    # 检查关联是否已存在
    stmt = select(RelationEntryFile).where(
        and_(
            RelationEntryFile.file_key == file_key,
            RelationEntryFile.entry_key == entry_key
        )
    )
    result = await db.execute(stmt)
    existing = result.scalar_one_or_none()
    
    if existing:
        return True  # 关联已存在
    
    # 创建关联
    stmt = insert(RelationEntryFile).values(
        file_key=file_key,
        entry_key=entry_key
    )
    await db.execute(stmt)
    
    return True 